package email;

import java.io.InputStream;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.Date;

import javax.mail.*;
import javax.mail.internet.*;

public class SendEmail {
	private static final String CONFIG_FILE = "/email.properties";
	private static final java.util.Properties appCfg = new java.util.Properties();
	
	public static void main(String[] argv) {
		try {
			
			try {
				InputStream stream = SendEmail.class.getResourceAsStream(CONFIG_FILE);
				if (stream != null){
					appCfg.load(stream);
				} else {
					System.out.println("SendEmail Input Stream is NULL");
				}
			} catch (Exception e) {
				System.err.println("SendEmail Error loading file " + CONFIG_FILE);
				System.err.println(e.getMessage());
				e.printStackTrace();
			}
			
			
			boolean ok = DBEmail.connect();
			if(ok == false){
				System.out.println(SendEmail.currentTimestamp() + " : " + DBEmail.getMessage());
				return;
			}
			
			Connection connection = DBEmail.getConnection();
			
			try {
				
				String sql = "SELECT * FROM NTFQUEUE WHERE NQ_STATUS = 'Pending' OR (NQ_STATUS = 'Failed' AND NQ_TRY < 3)";
				PreparedStatement stmt = connection.prepareStatement(sql);
		        
				try {
					String sql2 = "UPDATE NTFQUEUE SET NQ_STATUS = ?, NQ_STATUS_MSG = ?, NQ_TRY_DATE = sysdate, NQ_TRY = ?, NQ_SENT_DATE = ? WHERE NQ_ID = ?";
					PreparedStatement stmt2 = connection.prepareStatement(sql2);
				
			        try {
						ResultSet rs = stmt.executeQuery();
						
						try {
							
							String host = appCfg.getProperty("smtp_host");
							String port = appCfg.getProperty("smtp_port");
							String from = appCfg.getProperty("smtp_sender");
							String password = appCfg.getProperty("smtp_password");
							
							
							while( rs.next() ) {
								/*
								//Email settings
								String host = "smtp.gmail.com";
								String port = "465"; 							//there's another one down there
								String from = "username@gmail.com";		//Sender email
								String password = "password";				//Sender email password
								*/
								
								//Recipients and content
								String to = rs.getString("NQ_TO");			//TO Recipients
								String cc = "";		//CC Recipients
								String subject = rs.getString("NQ_SUBJECT");				//Email Subject
								String content = rs.getString("NQ_BODY");					//Email Content
								
								int trial = rs.getInt("NQ_TRY");							//Fail Trial
								trial = trial + 1;
								
								String send_status = SendEmail.sendEmail(host, port, from, password, to, cc, subject, content);
								String status = null;
								
								if (send_status.equals("OK")){
									status = "Sent";
									
									stmt2.setString(1, status);
									stmt2.setString(2, null);
									stmt2.setInt(3, trial);
									stmt2.setTimestamp(4, new Timestamp(System.currentTimeMillis()));
									stmt2.setString(5, rs.getString("NQ_ID"));
									
									System.out.println(SendEmail.currentTimestamp() + " : Email sent to " + to + " successfully!");
									
								} 
								else {
									status = "Failed";
									
									
									stmt2.setString(1, status);
									stmt2.setString(2, send_status);
									stmt2.setInt(3, trial);
									stmt2.setTimestamp(4, null);
									stmt2.setString(5, rs.getString("NQ_ID"));
									
									System.out.println(SendEmail.currentTimestamp() + " : Email sent to " + to + " failed!");
									
								}
								
								
								stmt2.executeQuery();
							}
							
							System.out.println(SendEmail.currentTimestamp() + " : Task completed.");
							
						}  finally {
							rs.close();
						}
						
			        } finally {
						stmt2.close();
					}
					
				} finally {
					stmt.close();
				}
				
			} finally {
				connection.close();
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		
	}
	
	public static String sendEmail(final String host, final String port, final String from, final String password, final String to, final String cc, final String subject, final String content) {
		
		System.out.println(SendEmail.currentTimestamp() + " : Attempting to send email...");
		
		Properties props = new Properties();
        props.put("mail.smtp.host" , host);

        //To use TLS
        props.put("mail.smtp.auth", "true"); 
        props.put("mail.smtp.starttls.enable", "true");
        
        //To use SSL
        props.put("mail.smtp.socketFactory.port", port);
        props.put("mail.smtp.socketFactory.class", "javax.net.ssl.SSLSocketFactory");
        props.put("mail.smtp.auth", "true");
        props.put("mail.smtp.port", port);


        Session session  = Session.getDefaultInstance( props ,
        		new javax.mail.Authenticator(){
            		protected PasswordAuthentication getPasswordAuthentication() {
            			return new PasswordAuthentication(from, password);	// Specify the Email and the PassWord
            		}
        		});
        
        
        Message msg = new MimeMessage(session);
        try {
            msg.setFrom(new InternetAddress(from));
            
            InternetAddress addressTo = new InternetAddress(to);
            msg.setRecipient(Message.RecipientType.TO, addressTo);
            
            /*InternetAddress addressCC = new InternetAddress(cc);
            msg.addRecipient(Message.RecipientType.CC, addressCC);*/
            
            msg.setSubject(subject);
            msg.setText(content);
            
            Transport transport = session.getTransport("smtps");
            transport.connect(host , Integer.parseInt(port) , from, password);
            transport.send(msg);
            
            return "OK";
        }
        catch(Exception exc) {
            System.out.println(currentTimestamp() + " : " + exc);
            return exc.toString();
        }
	}
		
	public static String currentTimestamp(){
		Date date = new Date();
		SimpleDateFormat sdf = new SimpleDateFormat("dd/MM/yyyy h:mm:ss a");
		String currentTimestamp = sdf.format(date);
		return currentTimestamp;
	}
}
